package constant.milk.periodapp.database;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import constant.milk.periodapp.bean.MemoData;
import constant.milk.periodapp.bean.SengriData;
import constant.milk.periodapp.bean.SexData;
import constant.milk.periodapp.util.MilkUtil;

public class DBSelectQuery
{
	private Context context;
	private DbAdapter db;

	public DBSelectQuery(Context context, DbAdapter db)
	{
		this.context = context;
		this.db = db;
	}

	public void setBeginTansaction() throws Exception
	{
		db.beginTansaction(context);
	}

	public void setSuccessful() throws Exception
	{
		db.setTransactionSuccessful();
	}

	public void setEndTansaction() throws Exception
	{
		db.endTransaction(context);
	}

	public ArrayList<MemoData> getMemoListAll()
	{
		ArrayList<MemoData> list = new ArrayList<MemoData>();

		String query = "";

		query = "SELECT * FROM " + TableName.MEMO
				+ " ORDER BY year DESC, month DESC, day DESC, id DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int memoIdx = c.getColumnIndex("memo");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			MemoData data = new MemoData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setMemo(c.getString(memoIdx));

			list.add(data);

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return list;
	}
	
	public ArrayList<MemoData> getMemoListMonth(int year, int month)
	{
		ArrayList<MemoData> list = new ArrayList<MemoData>();

		String query = "";

		query = "SELECT * FROM " + TableName.MEMO + " WHERE year = " + year
				+ " AND month = " + month
				+ " ORDER BY year DESC, month DESC, day DESC, id DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int memoIdx = c.getColumnIndex("memo");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			MemoData data = new MemoData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setMemo(c.getString(memoIdx));

			list.add(data);

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return list;
	}

	public ArrayList<MemoData> getMemoListDate(int year, int month, int day)
	{
		ArrayList<MemoData> list = new ArrayList<MemoData>();

		String query = "";

		query = "SELECT * FROM " + TableName.MEMO + " WHERE year = " + year
				+ " AND month = " + month + " AND day = " + day
				+ " ORDER BY year DESC, month DESC, day DESC, id DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int memoIdx = c.getColumnIndex("memo");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			MemoData data = new MemoData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setMemo(c.getString(memoIdx));

			list.add(data);

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return list;
	}

	public MemoData getMemoId(int id)
	{
		MemoData data = new MemoData();

		String query = "";

		query = "SELECT * FROM " + TableName.MEMO + " WHERE id = " + id;

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int memoIdx = c.getColumnIndex("memo");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setMemo(c.getString(memoIdx));

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return data;
	}

	public int getMemoCountDate(int year, int month, int day)
	{
		ArrayList<MemoData> list = new ArrayList<MemoData>();

		String query = "";

		query = "SELECT * FROM " + TableName.MEMO + " WHERE year = " + year
				+ " AND month = " + month + " AND day = " + day
				+ " ORDER BY year DESC, month DESC, day DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int memoIdx = c.getColumnIndex("memo");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			MemoData data = new MemoData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setMemo(c.getString(memoIdx));

			list.add(data);

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return list.size();
	}

	public int getSexCountDate(int year, int month, int day)
	{
		ArrayList<SexData> list = new ArrayList<SexData>();

		String query = "";

		query = "SELECT * FROM " + TableName.SEX_DATE + " WHERE year = " + year
				+ " AND month = " + month + " AND day = " + day
				+ " ORDER BY year DESC, month DESC, day DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			SexData data = new SexData();
			data.setId(c.getInt(idIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));

			list.add(data);

			c.moveToNext();
		}

		if (c != null)
			c.close();

		return list.size();
	}

	public int getSengriTermAverage()
	{
		int term = 0;
		
		String query = "";

		query = "SELECT AVG(term) term FROM " + TableName.PERIOD_DATE;

		Cursor c = db.select(context, query);

		int termIdx = c.getColumnIndex("term");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			term = c.getInt(termIdx);

			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return term;
	}
	
	public ArrayList<SengriData> getSengriList()
	{
		ArrayList<SengriData> list = new ArrayList<SengriData>();
		
		String query = "";

		query = "SELECT * FROM " + TableName.PERIOD_DATE + " ORDER BY date DESC";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			SengriData data = new SengriData();
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			list.add(data);
			
			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return list;
	}
	
	public ArrayList<SengriData> getSengriDate(int year, int month, int day)
	{
		ArrayList<SengriData> list = new ArrayList<SengriData>();
		
		String query = "";

		query = "SELECT * FROM " + TableName.PERIOD_DATE + " WHERE year = " + year
				+ " AND month = " + month + " AND day = " + day
				+ " ORDER BY year DESC, month DESC, day DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			SengriData data = new SengriData();
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			list.add(data);
			
			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return list;
	}
	
	public ArrayList<SengriData> getSengriDate2(int year1, int month1, int day1, int year2, int month2, int day2)
	{
		ArrayList<SengriData> list = new ArrayList<SengriData>();
		
		String query = "";

		query = "SELECT * FROM " + TableName.PERIOD_DATE + " WHERE"
				+ " date >= " + MilkUtil.getDateDB(year1, month1, day1) + " AND"
				+ " date <= " + MilkUtil.getDateDB(year2, month2, day2)
				+ " ORDER BY year DESC, month DESC, day DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			SengriData data = new SengriData();
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			list.add(data);
			
			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return list;
	}
	
	public ArrayList<SengriData> getSengriListYear(int year)
	{
		ArrayList<SengriData> list = new ArrayList<SengriData>();
		
		String query = "";

		query = "SELECT * FROM " + TableName.PERIOD_DATE  + " WHERE year = " + year
				+ " ORDER BY year DESC, month DESC, day DESC;";

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			SengriData data = new SengriData();
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			list.add(data);
			
			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return list;
	}
	
	public SengriData getSengriId(int id)
	{
		SengriData data = new SengriData();
		
		String query = "";

		query = "SELECT * FROM " + TableName.PERIOD_DATE  + " WHERE id = " + id;

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return data;
	}
	
	public SengriData getSengriMaxDate()
	{
		SengriData data = new SengriData();
		
		String query = "";

		query = "SELECT MAX(date) date, id, year, term, month, day FROM " + TableName.PERIOD_DATE;

		Cursor c = db.select(context, query);

		int idIdx = c.getColumnIndex("id");
		int termIdx = c.getColumnIndex("term");
		int yearIdx = c.getColumnIndex("year");
		int monthIdx = c.getColumnIndex("month");
		int dayIdx = c.getColumnIndex("day");
		int dateIdx = c.getColumnIndex("date");

		c.moveToFirst();

		for (int i = 0; i < c.getCount(); i++)
		{
			data.setId(c.getInt(idIdx));
			data.setTerm(c.getInt(termIdx));
			data.setYear(c.getInt(yearIdx));
			data.setMonth(c.getInt(monthIdx));
			data.setDay(c.getInt(dayIdx));
			data.setDate(c.getInt(dateIdx));

			c.moveToNext();
		}

		if (c != null)
			c.close();
		
		return data;
	}

	/**
	 * 해당 테이블에 데이터가 있는지 확인한다.
	 * 
	 * @param tableName
	 * @return
	 */
	public int getDBTableDataCount(String tableName)
	{
		String query = "";

		query = "SELECT * FROM " + tableName;

		Cursor c = db.select(context, query);

		int dbCount = 0;

		dbCount = c.getCount();

		c.close();

		return dbCount;
	}
}
